/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Dao;

import Entity.GiaoVienEntity;
import Util.ConSQL;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Thuyngaytho
 */
public class Giaovien {

    ConSQL cs = new ConSQL();

    //Them
     public ArrayList<GiaoVienEntity> getAll(){
         GiaoVienEntity gvEntity;
         ArrayList<GiaoVienEntity> list = new ArrayList<GiaoVienEntity>();
         try {
             PreparedStatement ps = cs.getCon().prepareStatement("select * from giaoVien");
             ResultSet rs = ps.executeQuery();
             while(rs.next()){
                 gvEntity = new GiaoVienEntity();
                 gvEntity.setMaGV(rs.getString("maGV"));
                 gvEntity.setTenGV(rs.getString("tenGV"));
                 gvEntity.setNgaySinh(rs.getString("ngaySinh"));
                 gvEntity.setGioiTinh(rs.getString("gioiTinh"));
                 gvEntity.setQueQuan(rs.getString("queQuan"));
                 gvEntity.setPhone(rs.getString("phone"));
                 gvEntity.setEmail(rs.getString("email"));
                 gvEntity.setsCMND(rs.getString("SCMND"));
                 
                 list.add(gvEntity);
             }
         } catch (Exception e) {
         }
         return list;
     }
     public ArrayList<GiaoVienEntity> timKiem(String str, int id){
         GiaoVienEntity gvEntity;
         ArrayList<GiaoVienEntity> list = new ArrayList<GiaoVienEntity>();
         try {
             PreparedStatement ps;
             if(id == 0){
                  ps = cs.getCon().prepareStatement("select * from giaoVien where maGV like N'%" + str + "%'");
             }else{
                 ps = cs.getCon().prepareStatement("select * from giaoVien where tenGV like N'%" + str + "%'");
             }
             ResultSet rs = ps.executeQuery();
             while(rs.next()){
                 gvEntity = new GiaoVienEntity();
                 gvEntity.setMaGV(rs.getString("maGV"));
                 gvEntity.setTenGV(rs.getString("tenGV"));
                 gvEntity.setNgaySinh(rs.getString("ngaySinh"));
                 gvEntity.setGioiTinh(rs.getString("gioiTinh"));
                 gvEntity.setQueQuan(rs.getString("queQuan"));
                 gvEntity.setPhone(rs.getString("phone"));
                 gvEntity.setEmail(rs.getString("email"));
                 gvEntity.setsCMND(rs.getString("SCMND"));
                 
                 list.add(gvEntity);
             }
         } catch (Exception e) {
         }
         return list;
     }
     //Het them. :D
     
    public boolean trungMa(String ma) throws Exception {
        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select maGV from giaoVien where maGV='" + ma + "';");
        if (rs.next() == true) {
            return true;
        } else {
            return false;
        }
    }

    public boolean trungMaSua(String mamoi, String macu) throws Exception {
        if (mamoi.equals(macu)) {
            return false;
        } else {
            Statement st = cs.getCon().createStatement();
            ResultSet rs = st.executeQuery("select maGV from giaoVien where maGV='" + mamoi + "';");
            if (rs.next() == true) {
                return true;
            } else {
                return false;
            }
        }
    }

    public boolean trungCMT(String cmt) throws Exception {
        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select maGV from giaoVien where SCMND='" + cmt + "';");
        if (rs.next() == true) {
            return true;
        } else {
            return false;
        }
    }

    public boolean trungCMTSua(String cmtMoi, String cmtCu) throws Exception {
        if (cmtMoi.equals(cmtCu)) {
            return false;
        } else {
            Statement st = cs.getCon().createStatement();
            ResultSet rs = st.executeQuery("select maGV from giaoVien where SCMND='" + cmtMoi + "';");
            if (rs.next() == true) {
                return true;
            } else {
                return false;
            }
        }
    }

    public int themGiaovien(String ma, String hoten, String ngaysinh, String gioitinh, String quequan, String dienthoai, String Email, String SCMND) throws Exception {
        int rs = 0;
        if (!trungMa(ma) && !trungCMT(SCMND)) {
            Statement st = cs.getCon().createStatement();
            rs = st.executeUpdate("insert into giaoVien values('" + ma + "',N'" + hoten + "','" + ngaysinh + "',N'" + gioitinh + "',N'" + quequan + "','" + dienthoai + "','" + Email + "','" + SCMND + "')");
        }
        return rs;
    }

    public boolean trungHocphan(String maLopHP, String maGV) throws SQLException, Exception {
        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select maGV from giaoVien_lopHP where SCMND='" + maLopHP + "' and maGV='" + maGV + "';");
        if (rs.next() == true) {
            return true;
        } else {
            return false;
        }
    }

    public void dangkimonday(String maLopHP, String maGV) throws SQLException, Exception {
        if (!trungHocphan(maLopHP, maGV)) {
            Statement st = cs.getCon().createStatement();
            int rs = st.executeUpdate("insert into giaoVien_lopHP values('" + maLopHP + "','" + maGV + "');");
        }
    }

    public int suaGiaovien(String ma, String hoten, String ngaysinh, String gioitinh, String quequan, String dienthoai, String email, String SCMND) throws SQLException, Exception {
        
        Statement st = cs.getCon().createStatement();
//        String sql = "update giaoVien set tenGV=N'" + hoten + "', ngaySinh='" + ngaysinh + "', gioiTinh=N'" + gioitinh + "', queQuan=N'" + quequan + "',phone='" + dienthoai + "', email='" + email + "', SCMND='" + SCMND + "' where maGV='" + ma + "'";
        String sql = "update giaoVien set tenGV=N'"+hoten+"', ngaySinh='"+ngaysinh+"', email='"+email+"', gioiTinh=N'"+gioitinh+"', phone='"+dienthoai+"', queQuan=N'"+quequan+"', SCMND='"+SCMND+"' where maGV='"+ma+"'";
        int rs = st.executeUpdate(sql);
        return rs;
    }

    public int xoaGiaovien(String ma) throws SQLException, Exception {
        Statement st = cs.getCon().createStatement();
        int rs = st.executeUpdate("delete from giaoVien where maGV='" + ma + "';");
        return rs;
    }
//    public String layMaLopSH(String tenLopSH,String tenNK) throws Exception{
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs= st.executeQuery("select malopSH from lopSH,nienKhoa where tenlopSH=N'"+tenLopSH+"' and nienKhoa.maNK=lopSH.maNK and tenNK='"+tenNK+"';");
//        while(rs.next()) return rs.getString(1);
//        return rs.getString(1);
//    }
    //tao cac tieu de cho bang

    public void createTitle(Vector tableTitle) {
        tableTitle.add("Mã giáo viên");
        tableTitle.add("Tên giáo viên");
        tableTitle.add("Ngày sinh");
        tableTitle.add("Giới tính");
        tableTitle.add("Quê quán");
        tableTitle.add("Điện thoại");
        tableTitle.add("Email");
        tableTitle.add("SCMND");

    }
    //tao cac row cho bang

    public void createRecord(Vector tableRecord, ResultSet rs) throws SQLException {
        while (rs.next()) {
            Vector data = new Vector();
            data.add(rs.getString(1));
            data.add(rs.getString(2));
            data.add(rs.getString(3));
            data.add(rs.getString(4));
            data.add(rs.getString(5));
            data.add(rs.getString(6));
            data.add(rs.getString(7));
            data.add(rs.getString(8));
//            data.add(rs.getString(9));
//            data.add(rs.getString(10));            
            tableRecord.add(data);
//            tableModel.addRow(data);
        }
    }
    //xem danh sach sinh vien theo ten khoa,ten lop,ten nien khoa

    public JTable xemDSGV(JTable table, Vector tableRecord, Vector tableTitle) throws Exception {
        tableRecord.removeAllElements();
        tableTitle.removeAllElements();

        createTitle(tableTitle);

        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select distinct maGV,tenGV,ngaySinh,gioiTinh,queQuan,phone,email,SCMND from giaoVien,giaoVien_lopHP, giaoVien_MH where giaoVien.maGV=giaoVien_lopHP.maGV and giaoVien_lopHP.maGV = giaoVien_MH.maGV;");

        createRecord(tableRecord, rs);
        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
        return table;
    }
//    public JTable timDSSVByTenkhoaTenLopSHvaTenNK(String tenkhoa,String tenlopSH,String tenNK,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maSV,hoTen,ngaySinh,gioiTinh,queQuan,phone,email,SCMND,doiTuong,ghiChu from sinhVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and khoa.tenkhoa=N'"+tenkhoa+"' and lopSH.maNK=nienKhoa.maNK and nienKhoa.tenNK='"+tenNK+"' and lopSH.tenlopSH=N'"+tenlopSH+"';");
//        createRecord(tableRecord, rs);        
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
//    public JTable timDSSVByTenKhoavaTenNK(String tenkhoa,String tenNK,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maSV,hoTen,ngaySinh,gioiTinh,queQuan,phone,email,SCMND,doiTuong,ghiChu from sinhVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and khoa.tenkhoa=N'"+tenkhoa+"' and lopSH.maNK=nienKhoa.maNK and nienKhoa.tenNK='"+tenNK+"';");
//        createRecord(tableRecord, rs);         
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
//    //tim theo ten khoa va ten lop sinh hoat
//    public JTable timDSSVByTenKhoavaTenLopSH(String tenkhoa,String tenLopSH,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maSV,hoTen,ngaySinh,gioiTinh,queQuan,phone,email,SCMND,doiTuong,ghiChu from sinhVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and khoa.tenkhoa=N'"+tenkhoa+"' and lopSH.maNK=nienKhoa.maNK and lopSH.tenlopSH=N'"+tenLopSH+"';");
//        createRecord(tableRecord, rs);        
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
    //tim theo ten khoa
//    public JTable timDSGVByTenKhoa(String tenkhoa,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maGV,tenGV,ngaySinh,gioiTinh,queQuan,phone,email,SCMND from giaoVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and khoa.tenkhoa=N'"+tenkhoa+"';");
//        createRecord(tableRecord, rs);        
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
//    //tim theo ten NK
//    public JTable timDSSVByNK(String tenNK,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maSV,hoTen,ngaySinh,gioiTinh,queQuan,phone,email,SCMND,doiTuong,ghiChu from sinhVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and lopSH.maNK=nienKhoa.maNK and nienKhoa.tenNK='"+tenNK+"';");
//        createRecord(tableRecord, rs);        
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
    //tim theo ma Sinh vien

    public JTable timDSGVByMaGV(String maGV, JTable table, Vector tableRecord, Vector tableTitle) throws Exception {
        tableRecord.removeAllElements();
        tableTitle.removeAllElements();

        createTitle(tableTitle);

        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select distinct maGV,tenGV,ngaySinh,gioiTinh,queQuan,phone,email,SCMND from giaoVien,giaoVien_MH, giaoVien_lopHP where giaoVien.maGV=giaoVien_MH.maGV and giaoVien_MH.maGV = giaoVien_lopHP.maGV and giaoVien.maGV='" + maGV + "';");
        createRecord(tableRecord, rs);
        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
        return table;
    }
    //tim theo ten sinh vien

    public JTable timDSGVByTenGV(String tenGV, JTable table, Vector tableRecord, Vector tableTitle) throws Exception {
        tableRecord.removeAllElements();
        tableTitle.removeAllElements();

        createTitle(tableTitle);

        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select distinct maGV,tenGV,ngaySinh,gioiTinh,queQuan,phone,email,SCMND from  giaoVien,giaoVien_MH, giaoVien_lopHP where giaoVien.maGV=giaoVien_MH.maGV and giaoVien_MH.maGV = giaoVien_lopHP.maGV and giaoVien.tenGV=N'" + tenGV + "';");
        createRecord(tableRecord, rs);
        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
        return table;
    }
    //tim theo ten lop sinh hoat
//    public JTable timDSSVByTenLopSH(String tenLopSH,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);                
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maSV,hoTen,ngaySinh,gioiTinh,queQuan,phone,email,SCMND,doiTuong,ghiChu from sinhVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and lopSH.maNK=nienKhoa.maNK and lopSH.tenlopSH=N'"+tenLopSH+"';");
//        createRecord(tableRecord, rs);        
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
//    //tim theo ten lop sinh hoat va nien khoa
//    public JTable timDSSVByTenLopSHvaTenNK(String tenLopSH,String tenNK,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{                
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//        
//        createTitle(tableTitle);
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maSV,hoTen,ngaySinh,gioiTinh,queQuan,phone,email,SCMND,doiTuong,ghiChu from sinhVien,lopSH,nienKhoa,khoa where sinhVien.malopSH=lopSH.malopSH and khoa.makhoa = lopSH.makhoa and lopSH.maNK=nienKhoa.maNK and lopSH.tenlopSH=N'"+tenLopSH+"' and nienKhoa.tenNK='"+tenNK+"';");
//        createRecord(tableRecord, rs);        
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
//    //xem Danh sach lop hoc phan theo makhoa va maNK    
//    public JTable xemDSLopHP(String makhoa,String maNK,JTable table) throws SQLException, Exception{
//        Vector tableTitle = new Vector();
//        Vector tableRecord = new Vector();
//        
//        tableRecord.removeAllElements();
//        tableTitle.removeAllElements();
//                        
//        tableTitle.add("Mã lớp học phần");
//        tableTitle.add("Tên lớp học phần");
//        tableTitle.add("Ngày bắt đầu");
//        tableTitle.add("Ngày kết thúc");
//        tableTitle.add("Mã khoa");
//        tableTitle.add("Mã niên khóa");
//        tableTitle.add("Mã môn");
//        tableTitle.add("Số tín chỉ");
//        
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct maLopHP,tenLopHP,ngayBatdau,ngayKetthuc,makhoa,maNK,lopHP.maMH,soTC from lopHP,monHoc where khoa.makhoa='"+makhoa+"' and nienKhoa.maNK='"+maNK+"' and lopHP.maMH=monhoc.maMH");
//        
//        while(rs.next()){
//            Vector data = new Vector();
//            data.add(rs.getString(1));
//            data.add(rs.getString(2));
//            data.add(rs.getString(3));
//            data.add(rs.getString(4));
//            data.add(rs.getString(5));
//            data.add(rs.getString(6));
//            data.add(rs.getString(7));
//            data.add(rs.getString(8));            
//            tableRecord.add(data);            
//        }                
//        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
//        return table;
//    }
//    public String LaytenKhoaByMaSV(String maSV) throws Exception{
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select khoa.tenkhoa from khoa,lopSH,sinhVien,nienKhoa where khoa.makhoa = lopSH.makhoa and lopSH.malopSH=sinhVien.malopSH and nienKhoa.maNK = lopSH.maNK and sinhVien.maSV='"+maSV+"'");        
//        if (rs.next()==false) return "";
//        return rs.getString(1);
//    }
//    public String LaytenLopSHByMaSV(String maSV) throws Exception{
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select lopSH.tenlopSH from khoa,lopSH,sinhVien,nienKhoa where khoa.makhoa = lopSH.makhoa and lopSH.malopSH=sinhVien.malopSH and nienKhoa.maNK = lopSH.maNK and sinhVien.maSV='"+maSV+"'");        
//        if (rs.next()==false) return "";
//        return rs.getString(1);
//    }
//    public String LaytenNKByMaSV(String maSV) throws Exception{
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select nienKhoa.tenNK from khoa,lopSH,sinhVien,nienKhoa where khoa.makhoa = lopSH.makhoa and lopSH.malopSH=sinhVien.malopSH and nienKhoa.maNK = lopSH.maNK and sinhVien.maSV='"+maSV+"'");        
//        if (rs.next()==false) return "";
//        return rs.getString(1);
//    }
//    public JComboBox cbbMakhoa(JComboBox cbb) throws Exception{
//        cbb.removeAllItems();
//        cbb.addItem("");
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select tenkhoa from khoa");
//        while (rs.next()){            
//            cbb.addItem(rs.getString(1));
//        }
//        return cbb;
//    }
//    
//    public JComboBox cbbLopSH(JComboBox cbb,String tenkhoa) throws Exception{
//        cbb.removeAllItems();
//        cbb.addItem("");
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select distinct tenlopSH from khoa,lopSH where lopSH.makhoa=khoa.makhoa and tenkhoa=N'"+tenkhoa+"';");
//        while(rs.next()){            
//            cbb.addItem(rs.getString(1));
//        }
//        return cbb;        
//    }

    public JComboBox cbbNgay(JComboBox cbb) {
        cbb.removeAllItems();
        for (int i = 1; i < 32; i++) {
            cbb.addItem(i);
        }
        return cbb;
    }

    public JComboBox cbbThang(JComboBox cbb) {
        cbb.removeAllItems();
        for (int i = 1; i < 13; i++) {
            cbb.addItem(i);
        }
        return cbb;
    }

    public JComboBox cbbNam(JComboBox cbb) {
        cbb.removeAllItems();
        for (int i = 1900; i < 3000; i++) {
            cbb.addItem(i);
        }
        return cbb;
    }
//    public JComboBox cbbNienkhoa(JComboBox cbb) throws Exception{
//        cbb.removeAllItems();
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select tenNK from nienKhoa");
//        cbb.addItem("");
//        while (rs.next()){
//            cbb.addItem(rs.getString(1));
//        }
//        return cbb;
//    }
    int ngay;

    public int layngay(String maGV) throws Exception {
        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select day(giaoVien.ngaySinh) from giaoVien where maGV='" + maGV + "';");

        if (rs.next()) {
            ngay = Integer.parseInt(rs.getString(1));
        }
        return ngay;
    }
    int thang;

    public int laythang(String maGV) throws Exception {
        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select month(giaoVien.ngaySinh) from giaoVien where maGV='" + maGV + "';");

        if (rs.next()) {
            thang = Integer.parseInt(rs.getString(1));
        }
        return thang;
    }
    int nam;

    public int laynam(String maGV) throws Exception {
        Statement st = cs.getCon().createStatement();
        ResultSet rs = st.executeQuery("select year(giaoVien.ngaySinh) from giaoVien where maGV='" + maGV + "';");

        if (rs.next()) {
            nam = Integer.parseInt(rs.getString(1));
        }
        return nam;
    }

    public boolean kiemtraSDT(String str) {
        if (str.length() != 10 && str.length() != 11 && !str.equals("")) {
            return false;
        } else {
            for (int i = 0; i < str.length(); i++) {
                if (!Character.isDigit(str.charAt(i))) {
                    return false;
                }
            }
        }
        return true;
    }

    public boolean kiemtraCMTND(String str) {
        if (str.length() != 9) {
            return false;
        } else {
            for (int i = 0; i < str.length(); i++) {
                if (!Character.isDigit(str.charAt(i))) {
                    return false;
                }
            }
        }
        return true;
    }
//    public String layMaNKByMaSV(String maSV) throws Exception{
//        Statement st = cs.getCon().createStatement();
//        ResultSet rs = st.executeQuery("select maNK from sinhVien,lopSH where sinhVien.malopSH=lopSH.malopSH and maSV='"+maSV+"'");
//        if(rs.next()){
//            return rs.getString(1);
//        }
//        return rs.getString(1);
//    }
}
